13
Organización de fichero:
Estructuración de los datos de un fichero en registros, bloques y estructuras de acceso
Técnica de organización de ficheros: cómo colocar los registros y bloques en el medio de almacenamiento y cómo interconectarlos
Objetivo: localización eficiente de los datos
Método de acceso:
Conjunto de programas que permite realizar operaciones sobre los datos de un fichero
Proporciona acceso a los datos; hace uso de las estructuras de acceso (si existen)
Combinados, deben maximizar la eficiencia de las operaciones más frecuentes sobre el fichero
Organización de ficheros vs. Método de acceso
Conceptos generales de organización de ficheros
14
Organización Primaria
Determina la forma en que los registros del fichero se colocan físicamente en el disco y cómo se puede acceder a ellos
Fichero No Ordenado (montón)
Fichero Ordenado (secuencial)
Fichero Mixto
Fichero Disperso (direccionamiento directo o hashing)
Árboles B y otras estructuras de datos (en el tema)
Organización Secundaria (o indexada)
Índice Ordenado de un nivel
Índice Multinivel
Tipos de organización de ficheros
Conceptos generales de organización de ficheros
15
Registros almacenados al final del fichero, en orden de inserción
Inserción muy eficiente
Búsqueda lineal
Eliminación física o por marca
Reorganización para recuperar espacio desocupado
O aprovechar huecos para nuevos registros
Modificación de un registro
Si longitud variable, puede provocar eliminación + inserción
Lectura ordenada supone creación de una copia ordenada del fichero
Suele utilizarse una técnica de ordenación externa
Fichero no ordenado (montículo o montón)
Organización primaria
16
Fichero ordenado (secuencial)
Organización primaria
Registros almacenados de forma ordenada según valores de cierto campo
campo de ordenación
Si el campo es clave, se le llama clave de ordenación
Ventajas respecto a los ficheros no ordenados
Lectura ordenada muy eficiente si el orden es el de los valores del campo de ordenación: lectura secuencial
Buscar siguiente (en el orden del campo de ordenación) no suele necesitar otro acceso a bloque
Buscar un registro dado su valor del campo de ordenación es rápido: búsqueda binaria
Pero si la Lectura ordenada o Buscar no están basados en el campo de ordenación, estas ventajas «desaparecen»
17
Fichero ordenado (2)
Organización primaria
Inserción
supone encontrar posición correcta para el registro y
abrirle espacio mediante desplazamiento de registros (costoso!)
Eliminación física o por marca
con problemas de eficiencia similares a la inserción
Para aumentar la eficiencia de la inserción
Dejar espacio libre en cada bloque para nuevos registros
Fichero de desbordamiento auxiliar, no ordenado, que periódicamente se ordena y fusiona con el fichero principal ? Buscar se complica: binaria en principal + lineal en auxiliar
Modificación
Si la condición de búsqueda se basa en el campo de ordenación, usar búsqueda binaria. Si no, búsqueda lineal
Si el campo modificado es el de ordenación, el nuevo valor puede provocar su cambio de ubicación: eliminación + inserción
18
Fichero ordenado (y 3)
Organización primaria
Fichero secuencial encadenado
Registros ordenados de forma lógica (no física)
Uso de punteros
Mayor flexibilidad
Estructuras de datos para implementar este tipo de ficheros
Listas lineales
Listas múltiples
Anillos o listas circulares
Árboles binarios de búsqueda
19
Fichero Mixto
Organización primaria
Almacena registros de diferente tipo
Los registros provienen de distintas tablas …
Están relacionadas mediante una o varias columnas
Clave externa y clave candidata
Las operaciones más frecuentes son las consultas y no es habitual modificar las columnas comunes
Solicitudes frecuentes de filas relacionadas de ambas tablas
Operaciones que implican la reunión (JOIN) de las tablas
En el fichero…
Los registros relacionados están físicamente adyacentes
clave del fichero mixto: campos «comunes» de los registros
Ventajas del uso de ficheros mixtos
Mayor eficiencia de las operaciones con JOIN entre las tablas
Valores de la clave del fichero mixto almacenados sólo una vez
En Oracle se denominan cluster de tablas
20
Organización primaria
Ejemplo de uso de un fichero mixtovs.dos ficheros individuales
21
Fichero de direccionamiento calculado (Hashing)
Organización primaria
Permite acceso rápido a registros según una condición de búsqueda de igualdad sobre un solo campo
Campo de dispersión (columna de dispersión)
Si es clave del fichero, se llama clave de dispersión
Función de dispersión h
Se aplica al valor k del campo de dispersión de un registro (fila)
Y produce la dirección del bloque de disco en el que está el registro
La localización del registro dentro del bloque se hace en el búfer
22
Fichero de direccionamiento calculado (2)
Organización primaria
Ejemplos de funciones h
Módulo: h(k) = k mod M
Plegado: Si k=k1k2k3k4, h(k)=k1k2?k3k4 , ? ? { + , xor }
Truncamiento: h(k) = k1k3
Desventaja del direccionamiento calculado
No se garantiza que valores distintos de k produzcan direcciones diferentes en el fichero
?Espacio de dispersión > Espacio de direcciones
Estudiaremos las técnicas de dispersión…
Interna
Externa
Dinámica
23
Fichero de direccionamiento calculado (3)
Organización primaria
Espacio de direcciones destino
Conjunto de M registros
Un array de M posiciones en memoria principal
Función de dispersión h
Traduce el valor del campo de dispersión en un entero ?[0, M-1]
Colisión
Al aplicar h a un registro que se desea insertar en el fichero, se obtiene una dirección ocupada
El nuevo registro es un sinónimo
Dispersión Interna
24
Fichero de direccionamiento calculado (4)
Organización primaria
Técnicas de resolución de colisiones
Direccionamiento abierto
Búsqueda secuencial de una posición vacía
Encadenamiento
Uso de listas de sinónimos en un área de desbordamiento
Dispersión múltiple
Aplicación de una segunda función h
Si se produce colisión, se aplica direccionamiento abierto
h debe ser sencilla y rápida de calcular
Su objetivo es conseguir una distribución uniforme de los registros (sin acumulamientos)
Mínimo número de colisiones
Máximo número de posiciones ocupadas
Dispersión Interna
25
Fichero de direccionamiento calculado (5)
Organización primaria
Espacio de direcciones destino
Conjunto de cubetas
Cubeta=grupo de registros del fichero (1 o más bloques contiguos)
Función de dispersión h
Traduce el valor del campo de dispersión en un número de cubeta
El descriptor de fichero contiene, para cada cubeta, la correspondencia número de cubeta ? dirección en disco
Decrece el número de colisiones
Los sinónimos se dispersan dentro de una cubeta
Uso de cubeta de desbordamiento (común al resto) y listas de registros sinónimos para insertar registros en cubetas ya completas
Dispersión Externa
26
Búsqueda o Localización
Acceso directo si condición de búsqueda basada en campo de dispersión ? el más rápido: 1 acceso a bloque
Si no, búsqueda lineal
Lectura ordenada
Costosa: h no suele mantener registros ordenados
Eliminación
Sacarlo de la cubeta y ocupar espacio con un sinónimo
O sacarlo de la lista de desbordamiento (si estaba ahí)
Modificación
Si la condición de búsqueda se basa en el campo de dispersión, usar h para localizar registro. Si no, búsqueda lineal
Si el campo modificado es el de dispersión, el nuevo valor puede provocar su cambio de cubeta: eliminación + inserción
Fichero de direccionamiento calculado (6)
Organización primaria
Dispersión Externa
27
Asigna al fichero una cantidad de espacio fija ?
M cubetas
Cada cubeta con capacidad para m registros
Espacio disponible = m*M registros
Número real de registros r
Si r < m*M ? gran desperdicio!
Si r > m*M ? muchas colisiones!
Largas listas de registros de desbordamiento
? lenta obtención de registros
Solución: dispersión dinámica
Fichero de direccionamiento calculado (7)
Organización primaria
Dispersión Externa
28
Fichero de direccionamiento calculado (8)
Organización primaria
Representación del resultado de la función de dispersión h como una cadena de bits
Valor de direccionamiento calculado del registro
Los registros se distribuyen en cubetas según los primeros bits de su valor de direcc. calculado
Tipos de dispersión dinámica
Direccionamiento calculado extensible
Direccionamiento calculado lineal
Dispersión Dinámica
?
29
Fichero de direccionamiento calculado (9)
Organización primaria
Fichero + Directorio
Directorio: array de 2d direcciones de cubeta
d es la profundidad global del directorio
Determinación de la posición de un registro:
El valor de los primeros d bits del valor de direccionamiento calculado indican una posición del directorio
Dicha posición contiene la dirección de cubeta en la que almacenar el registro
En el directorio…
Varias posiciones pueden contener la misma dirección de cubeta
La profundidad local d' de cada cubeta indica el nº de bits considerados: determina su contenido
Dispersión Dinámica: Direccionamiento Calculado Extensible
30
Fichero de direccionamiento calculado (10)
Organización primaria
Obtención de un registro
Dos accesos a bloque: directorio + cubeta
d puede disminuir o aumentar en 1 bit
? reducir a la mitad o duplicar el tamaño del directorio
Duplicar si se desborda una cubeta cuya (d' = d)
Reducir a la mitad si (d' < d) en todas las cubetas
Dispersión Dinámica: Direccionamiento Calculado Extensible
31
Fichero de direccionamiento calculado (y 11)
Organización primaria
Dispersión dinámica vs Dispersión estática
? Ventajas
El rendimiento no se degrada a medida que crece el fichero
No se asigna espacio para futuros crecimientos: se añaden cubetas adicionales conforme se necesitan
Espacio extra para directorio insignificante
Como mucho 2k, k = nº bits del valor de direccionamiento calculado
Reorganizaciones de registros «poco importantes»
Si se divide o duplica una cubeta, sólo se reorganizan sus registros
La más costosa: duplicación o reducción a la mitad del directorio
? Inconveniente (leve!)
Necesarios 2 accesos a bloque para localizar un registro
Dispersión Dinámica: Direccionamiento Calculado Extensible
32
Organización secundaria:Ficheros de índices
Un índice es una estructura de acceso adicional
Almacenado en disco
Utilizado junto con el fichero de datos (fichero principal)
Fichero principal estructurado según una organización primaria
Agiliza la obtención de registros según valores de cierto campo del fichero: campo de indexación
Primero se accede al índice, que apunta al bloque del fichero donde está almacenado el registro
Es posible crear …
un índice sobre cualquier campo de un fichero
varios índices sobre un mismo fichero
Un índice puede ser de uno de estos tipos
Ordenado de un nivel: Primario, de Agrupamiento, Secundario
De múltiples niveles: Árbol B, B+, B*
33
Organización secundaria
El fichero de índice contiene un conjunto de entradas
Cada entrada incluye dos campos, para almacenar…
Un valor de los almacenados en el campo de indexación
Un puntero al registro que contiene dicho valor o al bloque que lo contiene
Las entradas están ordenadas
Según valores extraídos del campo de indexación del fichero ppal
Es posible realizar búsquedas binarias sobre el índice
Índices densos y no densos
Denso si contiene una entrada por cada registro del fichero
En otro caso, es no denso (o disperso)
Operaciones sobre ficheros con índices:
Buscar implica búsqueda binaria sobre el índice
Insertar y Eliminar pueden provocar modificación del índice
Menos accesos a bloques, pero ? coste de mantener el índice
Índices ordenados de un nivel
34
Sobre ficheros con registros ordenados según los valores de un campo clave
El campo de indexación es dicha clave de ordenamiento
El índice es un fichero ordenado con…
Una entrada por cada bloque de fichero principal (índice no denso)
Cada entrada tiene longitud fija y 2 campos:
Valor del campo de indexación de un registro ancla de bloque
Puntero al comienzo de cada bloque
Bloques de índice < Bloques de fichero principal
Operaciones sobre ficheros con índices primarios:
Insertar y Eliminar pueden provocar la modificación del índice si afectan a los registros ancla
Índice primario
Organización secundaria
35
Sobre ficheros con registros ordenados según los valores de un campo no clave
El campo de indexación es dicho campo de agrupamiento
El índice es un fichero ordenado con…
Una entrada por cada valor distinto del campo de agrupamiento del fichero principal (índice no denso)
Cada entrada tiene longitud fija y 2 campos:
Valor distinto del campo de indexación (= de agrupamiento)
Puntero al primer bloque en que aparece dicho valor
Operaciones sobre ficheros con índices de agrupamiento:
Insertar y Eliminar costosas y provocan modificación del índice
Alivio si se reserva un bloque (del fichero de datos) para cada valor distinto del campo de agrupación (o varios bloques enlazados)
acelera la recuperación de registros que tienen el mismo valor en el campo de agrupamiento
Índice de agrupamiento
Organización secundaria
36
Sobre un campo que es clave del fichero principal, y queno marca la ordenación de los registros de datos
El campo de indexación es dicho campo: clave secundaria
El índice es un fichero ordenado con…
Una entrada por cada registro del fichero principal (índice denso)
Cada entrada tiene longitud fija y 2 campos:
Valor del campo de indexación (= clave secundaria)
Puntero al registro o al bloque en que aparece dicho valor
Operaciones sobre ficheros con índices de este tipo:
Insertar y Eliminar provocan la modificación del índice
Tiene más entradas que un índice primario
Ocupa más espacio. Mayor tiempo de búsqueda
Pero mayor "ganancia" respecto a búsqueda en el fichero de datos
Índice secundario sobre campo clave
Organización secundaria
37
Sobre un campo que no es clave del fichero principal, y queno marca la ordenación de los registros de datos
El campo de indexación es dicho campo
Opciones para crear el índice, fichero ordenado con…
Índice secundario sobre campo no clave
Organización secundaria
Una entrada por valor distinto del campo de indexación (no denso)
entradas de longitud variable: valor y varios punteros a bloque
Una entrada por cada registro del fichero principal (índice denso)
entradas de longitud fija y 2 campos: valor y puntero a registro
valores repetidos del campo de indexación
Una entrada por valor distinto del campo de indexación,y un nivel adicional de indirección (índice no denso)
entradas de longitud fija y 2 campos:
Valor del campo de indexación
Puntero a un bloque de punteros a registro
38
Se construye partiendo del índice sobre el fichero principal de datos en disco (índice de nivel base), mediante creación sucesiva de índices primarios sobre índices
El índice de nivel base puede ser de cualquier tipo, si éste…
tiene entradas de longitud fija y
almacena valores distintos del campo de indexación
El índice multinivel es denso si lo es el de nivel base
Se puede saber si un registro está en el fichero sin acceder a éste
Mejora el rendimiento de la búsqueda binaria: a cada paso desecha más entradas que en un índice de un solo nivel
La inserción, eliminación y modificación de registros de datos…
Son ineficientes si implican inserción o eliminación de entradas en el índice multinivel
Solución: índices dinámicos de múltiples niveles (árboles B, B+, B*)
Índice de múltiples niveles
Organización secundaria
39
Organización secundaria
Fichero totalmente invertido
Tiene un índice secundario sobre cada uno de sus campos
Los registros nuevos se insertan al final del fichero (no ordenado!)
Fichero secuencial indexado
Registros ordenados según valores de un campo clave y
Con un índice primario de múltiples niveles sobre dicha clave
Facilita acceso secuencial e individual (directo a través del índice)
Cada SGBDR comercial implementa los índices empleando sus propias técnicas
Oracle emplea, entre otras estructuras, la de árbol B*
Tipos de ficheros en función de su indexación
40
Especificación de índices en SQL
Antiguas versiones del SQL estándar contaban con sentencias para crear y destruir índices
El LDD no incluía cláusulas PRIMARY KEY o UNIQUE
restricción de integridad de clave mediante creación de índice único
La versión SQL-92 no considera los índices
Pues los índices son caminos físicos de acceso a los datos
Restricción de clave mediante cláusulas PRIMARY KEY y UNIQUE
La unicidad de una columna es un concepto lógico
Índices y el SQL estándar
41
Especificación de índices en SQL
En muchos SGBD comerciales, el índice …
es una estructura de datos independiente
se crea y destruye dinámicamente, sin afectar al fichero principal
Conviene crear un índice sobre cierto campo si se va a acceder con frecuencia al fichero según una condición de selección o reunión en la que aparezca dicho campo
? El índice incrementará la velocidad de esas consultas o accesos
? Cada índice sobre una columna hace que inserción, borrado y actualización sean más complejas y consuman más tiempo
Normalmente, el SGBD creará un índice secundario
Independiente de la ordenación física de los registros
Puede crearse con casi cualquier organización primaria de ficheros
Sobre un mismo fichero puede crearse varios índices secundarios
Índices en los SGBDR comerciales
42
Especificación de índices en SQL
Tipos de índices, según su implementación
Índices de árbol B, o normales; los creados por defecto
Índices de tablas
Índices de clusters
Índices bitmap
Índices particionados
Índices basados en funciones
Índices de dominio
Por defecto, Oracle almacena las filas de una tabla como una colección desordenada de filas
No cabe hablar de índices primarios o de agrupamiento, tal y como los hemos definido en este tema
Aunque es posible almacenar una tabla con una estructura de índice (árbol B*): index organized table
Índices en Oracle
?
43
Especificación de índices en SQL
Tipos de índices, según la columna (o campo) de indexación
Índice Único
Basado en una columna de tabla cuyos valores son únicos
Índice Primario (un caso particular de Índice Único)
Basado en una clave primaria de tabla
Índice Secundario
Basado en una columna con valores no únicos
Índice Compuesto, también Índice Concatenado
Basado en 2 o más columnas de una misma tabla
Puede ser Único, Primario o Secundario
Un Índice Compuesto Único es útil para imponer la unicidad de la combinación de valores de varias columnas
En Oracle el significado de los conceptos índice primario e índice secundario no coincide con la teoría explicada en el apartado 4.3 de este tema
Índices en Oracle (2)
44
Especificación de índices en SQL
Sentencia LDD para crear índices
CREATE INDEX Indice_Genero_Pelicula ON Pelicula ( genero ) ;
Especificación del orden ascendente o descendente de las entradas del índice
CREATE INDEX Indice_Año_Pelicula ON Pelicula ( año DESC ) ;
Índice compuesto
CREATE INDEX Indice_Nombres_Director ON Director ( apellido1 ASC, apellido2 ASC, nombre DESC ) ;
El índice incrementa la velocidad de las consultas que acceden por…
apellido1
apellido1 y apellido2
apellido1 y apellido2 y nombre
Pero el SGBD no usará el índice para consultas que acceden por…
apellido2
nombre
apellido2 y nombre
Índices en Oracle (3)
45
Especificación de índices en SQL
Algunos SGBDR, como Oracle, crean un índice sobre cada clave (primaria o alternativa) de forma automática
Campo de indexación: columna (o conjunto de columnas) especificada como UNIQUE o PRIMARY KEY en el esquema de BD
El SGBD empleará el índice para imponer la restricción de clave
Creación explícita de un índice único
Usado para imponer una restricción de clave sobre una o varias columnas
CREATE UNIQUE INDEX Indice_Director ON Director(apellido, fechanacim) ;
En el caso de que la tabla DIRECTOR ya contenga datos, este índice sólo será creado si cada combinación de valores apellido y fechanacim es distinta para todas las filas de la tabla
Oracle recomienda la creación explícita del índice, y no sólo la declaración de la clave como UNIQUE en la especificación de la tabla
Índices en Oracle (4)
46
Especificación de índices en SQL
Sentencia LDD para destruir índices
DROP INDEX Índice_Genero_Película ;
Conviene eliminar un índice cuando ya no se espera realizar consultas basadas en el campo de indexación
Desaparece el coste de mantenimiento del índice
Se recupera el espacio de almacenamiento ocupado por el índice
No se puede eliminar un índice creado automáticamente por el sistema (debido a la especificación de una restricción de integridad UNIQUE o PRIMARY KEY sobre una o varias columnas), sino que se debe eliminar (DROP) o desactivar (DISABLE) la restricción de integridad correspondiente
Índices en Oracle (y 5)
47
La mayoría de SGBDR representa cada tabla base como un fichero de BD, para el cual es necesario especificar…
Tipo de fichero,
Columnas sobre los que definir estructuras de acceso (índices)
Decisiones de diseño físico
Sobre estructuras de almacenamiento (organización de ficheros)
Sobre índices (estructuras de acceso)
Selección de la organización de ficheros y estructuras de acceso
Selección: decisiones de diseño físico
48
Cuándo emplear un fichero no ordenado
La tabla es pequeña
Registros pequeños o factor de bloques elevado
Es inspeccionada con pocos accesos a bloque
Normalmente, las consultas u operaciones más frecuentes sobre la tabla seleccionan o acceden a muchas filas (es decir, más del 20% del total de las filas)
Selección de la organización de ficheros y estructuras de acceso
Sobre estructuras de almacenamiento
49
Cuándo emplear un fichero ordenado
La tabla es de tamaño medio-grande y con frecuencia es…
ordenada según valores de una columna c
c aparece en cláusulas ORDER BY, GROUP BY, DISTINCT, o como columna de REUNIÓN o de UNION
accedida con criterios de selección que incluyen un rango de valores de c
procesada secuencialmente en el orden de los valores de c (lectura ordenada)
La tabla no es frecuentemente modificada con INSERT, UPDATE (c) o DELETE
Selección de la organización de ficheros y estructuras de acceso
Sobre estructuras de almacenamiento
50
Cuándo emplear un esquema de dispersión
Los accesos a la tabla son frecuentes, individuales y aleatorios, y según valores de una columna c, que…
Es muy utilizada en operaciones de selección por igualdad
Es muy usada en condiciones de reunión (JOIN) – equi-reunión –
Sus valores apenas son modificados
Los valores de c son muchos y variados: permiten una buena distribución de los registros, evitando colisiones
Se conoce el tamaño del fichero y no se espera un crecimiento o reducción considerable
En caso contrario, puede usarse dispersión dinámica
Selección de la organización de ficheros y estructuras de acceso
Sobre estructuras de almacenamiento
51
Cuándo evitar un esquema de dispersión
Los accesos a la tabla son
por rango de valores de a, salvo si es una lista: a IN (v1, v2, …vn)
con criterios de selección que no incluyen a, o incluyen sólo parte de a
en orden, según los valores de a
No todos los SGBD soportan dispersión (Oracle sí)
Puede definirse un solo esquema hashing por tabla
No es posible combinar dispersión y ordenación: ambos determinan la posición física de los registros en el fichero
Selección de la organización de ficheros y estructuras de acceso
Sobre estructuras de almacenamiento
52
Cuándo emplear un fichero mixto (cluster de tablas)
Son muy frecuentes las consultas que implican…
La reunión entre dos (o más) tablas relacionadas
Ejemplo: relación 1:N vía clave primaria/clave externa
El agrupamiento de filas de la tabla del lado N, según valor de la clave externa
En Oracle crear un fichero mixto significa crear un cluster, definir clave de cluster, crear índice cluster, y almacenar las tablas dentro
? Ventajas
Acelera la ejecución de las reuniones entre las tablas
registros relacionados están en el mismo bloque ? menos accesos a bloque
Aprovechamiento del espacio
Selección de la organización de ficheros y estructuras de acceso
Sobre estructuras de almacenamiento
53
Cuándo emplear un fichero mixto (cont.)
? Inconvenientes
Consultas a cada tabla por separado muy poco eficientes
Recorrido completo de las filas de cada tabla individual, más costoso (demasiados accesos a bloque)
La inserción es poco eficiente debido a que se debe…
mantener la agrupación física (filas con igual valor de clave de cluster), y a la vez
desaprovechar el mínimo espacio de almacenamiento
?muchos encadenamientos entre registros (zonas de desborde, etc.)
?menor eficiencia de la búsqueda según la clave del cluster
?reducción del rendimiento de las consultas
Selección de la organización de ficheros y estructuras de acceso
Sobre estructuras de almacenamiento
54
Cuándo emplear un índice
La tabla es de tamaño medio/grande: Si la tabla es pequeña, resulta más eficiente cargarlas en memoria
Las consultas más frecuentes acceden a pequeños porcentajes del total de filas (=20%): ¿mejor recorrido secuencial para acceder a muchas filas?
Se desea evitar…
el recorrido de la tabla completa: usamos búsqueda binaria en el índice
Selección de la organización de ficheros y estructuras de acceso
Sobre índices
55
Cuándo emplear un índice
(continuación) Se desea evitar…
el acceso al fichero para determinadas consultas (EXISTS, IN, …) :si el índice es denso basta con acceder a él para saber si existe
la ordenación de los registros en el fichero (ORDER BY, GROUP BY, UNION, DISTINCT, JOIN):
al crear un índice primario o de agrupamiento el fichero se almacena ordenado
Al tener un índice ordenado se puede utilizar para operaciones que impliquen ordenación
el acceso al fichero para consultas que incluyen un pequeño subconjunto de columnas: usar índices compuestos
Sea la tabla PERSONA(nif, nombre, apellido, fecha_nacim, ciudad, telef)
CREATE INDEX idx_persona ON PERSONA( apellido, nombre );
Selección de la organización de ficheros y estructuras de acceso
Sobre índices
56
Cuándo indexar una columna
Es una clave
Muy usada en condiciones de reunión o de selección
Suele aparecer en cláusulas WHERE en comparaciones de igualdad o rango de valores: =, >, < , ?, ?, BETWEEN
Suele ser columna de reunión (join) de tablas (ej. claves ajenas)
Tiene gran variedad de valores (?más discriminación en búsquedas)
No es modificada muy a menudo
Emplear un índice compuesto si habitualmente se accede por medio de varias columnas utilizadas conjuntamente
El SGBD sólo usará el índice para consultas que incluyan (en WHERE) condiciones sobre campos que forman un prefijo de la clave de indexación
Selección de la organización de ficheros y estructuras de acceso
Sobre índices
57
Cuándo evitar un índice
La tabla es muy pequeña: cargarla en memoria
Sobre columnas…
modificadas muy a menudo
con distribución irregular de valores: confunden al Optimizador
que sólo aparecen en funciones/operadores (distintos de MAX o MIN): pueden hacer que el índice no se use
Se degradan los requisitos de procesamiento crítico: si se le da al SGBD muchos índices a elegir
El costo(almacenamiento+mantenimiento) > beneficio
INSERT, UPDATE, DELETE sobre tablas indexadas ?mantenimiento del índice (automático, por parte del SGBD)
A veces conviene…
crear la tabla, rellenarla, y luego crear los índices
eliminar índices, modificar datos y crear de nuevo los índices
Selección de la organización de ficheros y estructuras de acceso
Sobre índices
58
Selección de la organización de ficheros y estructuras de acceso
Sobre índices
Consejos para la utilización de índices
Máximo 4 índices por tabla
índices ? ? necesidad de espacio y ? velocidad al modificar
el SGBD puede crear implícitamente índices sobre las claves
Se puede crear más, si es raro actualizar la tabla (histórico)
Almacenar índices secundarios en un espacio de almacenamiento distinto al de los datos
Recomendación para aplicaciones con muchas INSERT y DELETE
Índices primario o de agrupamiento en el mismo área que los datos
La mayoría de SELECT y todas las INSERT, DELETE los leen
Índices secundarios en un espacio de almacenamiento diferente
Mejor balanceo de carga: INSERT, DELETE actualizan el índice
Un índice Primario o de Agrupamiento no tiene por qué verse modificado con cada INSERT, DELETE o UPDATE(columnas_indexación), mientras que uno Secundario con seguridad sí deberá ser modificado.
59
Ejercicio
Dadas las siguientes situaciones, determinar el método de acceso más apropiado (exploración, cluster, índices, tabla hash para cada tabla o conjunto de tablas). Justificar las respuestas.
Tenemos 3 tablas A, B y C, de forma que B y C tienen como clave ajena un atributo de A. Las consultas son mayoritariamente sobre la tabla A, pero no a través de la clave primaria ni de un atributo de ordenación. Se estima que las tablas serán de tamaño reducido.
Tenemos las 3 tablas del apartado anterior. Las consultas son mayoritariamente joins sobre el atributo que ejerce como clave ajena en B y C y como primaria en A. Las consultas individuales sobre cada tabla individual son irrelevantes.
Tenemos una tabla de 4 atributos con muchas tuplas. Existen muchas consultas de modificación del atributo que genera la mejor ordenación sobre la tabla.
Tenemos la misma tabla del apartado anterior. Existen dos tipos mayoritarios de consultas: consultas sobre rangos de valores sobre la clave primaria y consultas que requieren de la ordenación de las tuplas obtenidas.
60
Estructura lógica de una BD Oracle
61
Estructuras lógicas de almacenamiento
Segmento datos
Segmento de índices
Segmento de rollback
Segmento temporal
SELECT … ORDER BY…
CREATE INDEX.
SELECT … GROUP BY…
SELECT … UNION …
SELECT DISTINCT …
SELECT . INSERSEC …
SELECT … MINUS …
62
Estructura física de Oracle
Ficheros de datos
Ficheros redo log
Ficheros de control
63
Entorno de memoria en Oracle
SGA: System/Shared Global Area
Shared pool: library (zona sql, control y bloqueos)+dictionary (metadatos) cache
Database Buffer Cache
Redo Log Buffer
PGA: Program Global Area
64
Elementos de diseño físico en ORACLE
Tablespace
CREATE TABLESPACE TS_DATOS
DATAFILE '/disco1/fichero1' SIZE 100 M,
DATAFILE '/disco2/fichero2' SIZE 250 M;
Tablas y extensiones
CREATE TABLE Alumnos (.) TABLESPACE TS_DATOS
PCTFREE 20 PCTUSED 40
STORAGE (INITIAL 20K NEXT 30K MINEXTENTS 1 MAXEXTENTS 10 PCTINCREASE 0);
65
Elementos de diseño físico en ORACLE
Índices (dentro de create table)
…
CREATE INDEX nom_ind ON Alumnos (atributos)
..
Clusters
CREATE CLUSTER CL_1(clave number(4)) SIZE 512 TABLESPACE TS_DATOS PCTFREE 20 STORAGE(…);
CREATE TABLE Alumno (..) CLUSTER CL1_(a1);
66
Clusters
CREATE CLUSTER Emp_Dept (departamento CHAR(4)) SIZE 512.;
CREATE TABLE Departamento {
codigo CHAR (4) PRIMARY KEY,
…….
) CLUSTER Emp_Dept(codigo);
CREATE TABLE EmpleadoFijo {
nss CHAR(12) PRIMARY KEY,
…..
dpto CHAR (4) NOT NULL REFERENCES Departamento(codigo)
…….
) CLUSTER Emp_Dept(dpto);
CREATE INDEX idx_Emp_Dpt ON CLUSTER Emp_Dept;
67
Dispersión
CREATE CLUSTER Direcciones (codpostal NUMBER(5))
.SIZE 2k
HASH IS MOD(codpostal, 101)
HASHKEYS 1000;
 Página anterior | Volver al principio del trabajo | Página siguiente  |